package bancodedados;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;
import sistemapatrimonio.*;

public class ConectaBanco {

    private static Connection conexao;
    private Statement smt;
    private PreparedStatement pSmt;

    public ConectaBanco() {
        conecta();
    }

    public static Connection conecta() {
        String driver = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/patrimonio";
        String login = "root";
        String senha = "root";

        try {
            Class.forName(driver);
            ConectaBanco.setConexao(DriverManager.getConnection(url, login, senha));
            System.out.println("Conexão bem sucedida!");
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("Não foi possível conectar 1");
        }
        return ConectaBanco.getConexao();
    }

    public boolean insert(String script) {
        try {
            setSmt(getConexao().createStatement());
            getSmt().executeUpdate(script);
            return true;
        } catch (SQLException e) {
            System.out.println("erro sql conexao: " + e);
            return false;
        }
    }
    

    public static void listar_pesquisa(String select, String from) throws Exception {
        Statement st;
        st = conexao.createStatement();
        ResultSet rs = st.executeQuery("SELECT " + select + " FROM " + from);
        System.out.println("--------------------");
        while (rs.next()) {
            System.out.println("codigo" + rs.getString("nome") + "vdfgsfsd...");
        }
    }
/*
    public void executa(String sql) {
        try {
            Statement st = null;
            st = conexao.createStatement();
            st.executeUpdate(sql);
        } catch (SQLException e) {
            System.out.println("Erro pra executar algum comando SQL!");
        }
    } */

    public static List<Usuario> pesquisarFuncionario(String select, String from) throws Exception {
        Statement st;
        List<Usuario> lista = new ArrayList<>();
        st = conexao.createStatement();
        ResultSet rs = st.executeQuery("SELECT " + select + " FROM " + from);
        while (rs.next()) {
            Usuario user = new Usuario();
            user.setNome(rs.getString("nome"));
            user.setCpf(rs.getString("cpf"));
            user.setSiape(rs.getInt("siape"));
            user.setSenha(rs.getString("senha"));
            
            lista.add(user);
        }
        return lista;
    }

    public static List<BemPatrimonial> removerPNaoEn(String select, String from,
            String valor, List<BemPatrimonial> lista) throws Exception {

        Statement st;
        int cont = 0;
        st = conexao.createStatement();
        ResultSet rs = st.executeQuery("SELECT " + select + " FROM " + from);
        while (rs.next()) {
            if (select.equalsIgnoreCase("Plaqueta")) {
                if (lista.get(cont).getPlaqueta() != rs.getInt(select)) {
                    lista.remove(cont);
                }
            } else if (select.equalsIgnoreCase("situacao")) {
                if (lista.get(cont).getSituacao().
                        equalsIgnoreCase(rs.getString(select)) == false) {

                    lista.remove(cont);
                }
            } else if (select.equalsIgnoreCase("Funcionario_siape")) {
                String temp = Integer.toString(lista.get(cont).getResponsavel().getUsuário().getSiape());
                if (temp.equalsIgnoreCase(rs.getString(select)) == false) {

                    lista.remove(cont);
                }
            } else if (select.equalsIgnoreCase("Funcionario_siape1")) {
                String temp = Integer.toString(lista.get(cont).getResponsavel().getUsuário().getSiape());
                if (temp.equalsIgnoreCase(rs.getString(select)) == false) {

                    lista.remove(cont);
                }
            } else if (select.equalsIgnoreCase("NotaFiscal_idNotaFiscal")) {
                if (lista.get(cont).getNotaFiscal().getNumero().
                        equalsIgnoreCase(rs.getString(select)) == false) {

                    lista.remove(cont);
                }
            } else if (select.equalsIgnoreCase("Local_idLocal")) {
                int id = ConectaBanco.getId("*", "local", lista.get(cont).
                        getLocalAtual().getSala(), "sala");
                if (id != rs.getInt("Local_idLocal")) {
                    lista.remove(cont);
                }
            } else if (select.equalsIgnoreCase("dataCadastro")) {
                if (lista.get(cont).getDataCadastro().
                        equalsIgnoreCase(rs.getString(select)) == false) {

                    lista.remove(cont);
                }
            }

            cont++;
        }
        return lista;
    }

    public static List<BemPatrimonial> pesquisarPatrimonio(String select, String from) throws Exception {
        Statement st;
        List<BemPatrimonial> lista = new ArrayList<>();
        st = conexao.createStatement();
        ResultSet rs = st.executeQuery("SELECT " + select + " FROM " + from);
        while (rs.next()) {
            String nome;
            Usuario user1 = new Usuario();
            nome = ConectaBanco.getNome("*", "funcionario", rs.getInt("Funcionario_siape"), "nome");
            user1.setNome(nome);
            user1.setSiape(rs.getInt("Funcionario_siape"));

            Usuario user2 = new Usuario();
            nome = ConectaBanco.getNome("*", "funcionario", rs.getInt("Funcionario_siape1"), "nome");
            user2.setNome(nome);
            user2.setSiape(rs.getInt("Funcionario_siape1"));

            Responsavel resp1 = new Responsavel();
            resp1.setUsuário(user1);

            Responsavel resp2 = new Responsavel();
            resp2.setUsuário(user2);

            NotaFiscal nt = new NotaFiscal();
            nt.setNumero(rs.getString("NotaFiscal_idNotaFiscal"));

            Local local = new Local();
            nome = ConectaBanco.getNome("*", "local", rs.getInt("Local_idLocal"), "sala");
            local.setSala(nome);

            nome = ConectaBanco.getNome("*", "local", rs.getInt("Local_idLocal"), "predio");
            local.setPredio(nome);

            BemPatrimonial bem = new BemPatrimonial(rs.getString("situacao"), nt, local, resp2, resp2);
            bem.setPlaqueta(rs.getInt("Plaqueta"));
            bem.setDataCadastro(rs.getString("dataCadastro"));
            lista.add(bem);
        }
        return lista;
    }
    
    public static List<BemPatrimonial> pesquisaPatrimonio(String select, String from,String valor) throws Exception{ 
        Statement st;
        List<BemPatrimonial> lista = new ArrayList<>();
        st = conexao.createStatement();
        ResultSet rs = st.executeQuery("SELECT * FROM " + from + " WHERE "+ select + " = "+ valor);
        while (rs.next()){
            String nome;
            Usuario user1 = new Usuario();
            nome = ConectaBanco.getNome("*", "funcionario", rs.getInt("Funcionario_siape"), "nome");
            user1.setNome(nome);
            user1.setSiape(rs.getInt("Funcionario_siape"));

            Usuario user2 = new Usuario();
            nome = ConectaBanco.getNome("*", "funcionario", rs.getInt("Funcionario_siape1"), "nome");
            user2.setNome(nome);
            user2.setSiape(rs.getInt("Funcionario_siape1"));

            Responsavel resp1 = new Responsavel();
            resp1.setUsuário(user1);

            Responsavel resp2 = new Responsavel();
            resp2.setUsuário(user2);

            NotaFiscal nt = new NotaFiscal();
               

            nt.setNumero(rs.getString("NotaFiscal_idNotaFiscal"));

            Local local = new Local();
            nome = ConectaBanco.getNome("*", "local", rs.getInt("Local_idLocal"), "sala");
            local.setSala(nome);

            nome = ConectaBanco.getNome("*", "local", rs.getInt("Local_idLocal"), "predio");
            local.setPredio(nome);

            BemPatrimonial bem = new BemPatrimonial(rs.getString("situacao"), nt, local, resp2, resp2);
            bem.setPlaqueta(rs.getInt("Plaqueta"));
            lista.add(bem);
        }
        return lista;
    }

    public static List<Local> pesquisarLocal(String select, String from) throws Exception {
        Statement st;
        List<Local> lista = new ArrayList<>();
        st = conexao.createStatement();
        ResultSet rs = st.executeQuery("SELECT " + select + " FROM " + from);
        while (rs.next()) {
            Local local = new Local();
            local.setPredio(rs.getString("predio"));
            local.setSala(rs.getString("sala"));
            local.setAmbienteExterno(rs.getBoolean("ambienteExterno"));
            lista.add(local);
        }
        return lista;
    }

    public static List<NotaFiscal> pesquisarNotaFiscal(String select, String from) throws Exception {
        Statement st;
        List<NotaFiscal> lista = new ArrayList<>();
        st = conexao.createStatement();
        ResultSet rs = st.executeQuery("SELECT " + select + " FROM " + from);
        while (rs.next()) {
            NotaEmpenho ne = new NotaEmpenho();
            NotaFiscal nota = new NotaFiscal();
            nota.setNumero(rs.getString("idNotaFiscal"));
            nota.setValor(rs.getString("valor"));
            ne.setNumeroEmpenho(rs.getString("NotaEmpenho_idNotaEmpenho"));
            nota.setNotaEmpenho(ne);
            lista.add(nota);
        }
        return lista;
    }

    public static List<String> pesquisarObjeto(String select, String from) throws Exception {
        Statement st;
        List<String> lista = new ArrayList<>();
        if (select.equalsIgnoreCase("*")) {
            return null;
        }
        st = conexao.createStatement();
        ResultSet rs = st.executeQuery("SELECT " + select + " FROM " + from);
        while (rs.next()) {
            lista.add(rs.getString(select));
        }
        return lista;
    }

    public static int getId(String select, String from, String nome, String coluna) throws Exception {
        Statement st;
        int result = -1;
        st = conexao.createStatement();
        ResultSet rs = st.executeQuery("SELECT " + select + " FROM " + from);
        while (rs.next()) {
            if (nome.equalsIgnoreCase(rs.getString(coluna))) {
                result = rs.getInt(1);
                return result;
            }
        }
        return result;
    }

    public static String getNome(String select, String from, int id, String coluna) throws Exception {
        Statement st;
        String result = "";
        st = conexao.createStatement();
        ResultSet rs = st.executeQuery("SELECT " + select + " FROM " + from);
        while (rs.next()) {
            if (id == rs.getInt(1)) {
                result = rs.getString(coluna);
                return result;
            }
        }
        return result;
    }
    
    

    public boolean insertPatrimonio(String script) {
        try {
            pSmt = getConexao().prepareStatement(script);
            pSmt.executeUpdate();
            return true;
        } catch (SQLException e) {
            System.out.println("erro sql conexao: " + e);
            return false;
        }
    }
    
    public static String pegaMaterial(NotaFiscal nt){
        String tipo = null;
        Statement st;
        String temp = null;
        try {
            st = conexao.createStatement();
             ResultSet rs1 = st.executeQuery("SELECT Material_idMaterial"
                     + " FROM  notaempenho WHERE idNotaEmpenho = " 
                     + nt.getNotaEmpenho().getNumeroEmpenho());
             while(rs1.next()){
                 temp = rs1.getString("Material_idMaterial");
             }
             
             ResultSet rs2 = st.executeQuery("SELECT tipo"
                     + " FROM  material WHERE idMaterial = " 
                     + temp);
             
             while(rs2.next()){
                 tipo = rs2.getString("tipo");
             }
             return tipo;
        } catch (SQLException ex) {
            
        }
        return null;
    }

    public static void desconecta() throws SQLException {
        conexao.close();
    }

    public Statement getSmt() {
        return smt;
    }

    public void setSmt(Statement smt) {
        this.smt = smt;
    }

    public static Connection getConexao() {
        return conexao;
    }

    public static void setConexao(Connection aConexao) {
        conexao = aConexao;
    }
}
